package ReSofting.it.GoldDirector.Db;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.Time;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
    private static final int DB_VERSION = 1;
    public  static final String DB_NAME = "myDB.db";
    private static final String DB_PATH = "/data/data/ReSofting.it.GoldDirector/databases/";
    
    private SQLiteDatabase _DataBase;     
    private final Context _Context;

    public DatabaseHelper(Context ctx) {
        super(ctx, DB_NAME, null, DB_VERSION);
        this._Context = ctx;
                           
      
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       
    	Log.d("Diego in onCreate", "");
    	
    	if (db.isOpen()){
            Log.d("In onCreate", "OK.. Database open");
        }

        if (db.isReadOnly()){
            Log.e("In onCreate", "The database is read only");
        }
    	
        db.beginTransaction();
        try {        
        
		db.execSQL("DROP TABLE IF EXISTS TestTable " );
		db.execSQL("DROP TABLE IF EXISTS Settings " );
    	db.execSQL("DROP TABLE IF EXISTS Categories" );
    	db.execSQL("DROP TABLE IF EXISTS MenuItems" );
    	db.execSQL("DROP TABLE IF EXISTS Orders" );
    	
    	String query = "CREATE TABLE TestTable( ID INTEGER PRIMARY KEY AUTOINCREMENT, txt TEXT NOT NULL, num INTEGER NOT NULL, datas TEXT )";
        db.execSQL(query);
        
    	query = "CREATE TABLE Settings( prop TEXT NOT NULL, value Text NOT NULL )";
        db.execSQL(query);        
        
        
        query = "CREATE TABLE NetSettings( HostIp TEXT NOT NULL, Port Text NOT NULL , ServiceCore Text NOT NULL, ServiceLogin Text NOT NULL)";
        db.execSQL(query); 
        
        query = "CREATE TABLE Categories(Nome text not null, AreaProd text not null)";
        db.execSQL(query);
        
        query = "CREATE TABLE Aggiunte(IdAgg int not null, Nome text not null, FkCat text not null)";
        db.execSQL(query);
        
        query = "Create table MenuItems (Guid Text not null, Categoria text not null, Name text not null)";
        db.execSQL(query);
        
        query = "Create table Orders (Guid Text not null, TableNum text not null, TotCustomers integer not null, " +
        		"TotCoperti integer not null, Sended integer, Time text )";
        db.execSQL(query);
        
        db.setTransactionSuccessful();
        
        } 
        finally {
            db.endTransaction();
          }
        
        Log.d("Diego in DbHelper", "after db creation");
    }


	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		

    	
    	onCreate(db);
    	
		Log.d("On Upgrade Db", "");
		
	}//fM
	
	public void execSqlNoRet(String pSql)
	{
		openDataBase();
		_DataBase.execSQL(pSql);		
	}
	
	
	public Cursor ExecSql(String pSql)
	{
		try {
			openDataBase();
			
			return _DataBase.rawQuery(pSql, null);
		} catch (Exception e) {
			Log.d("ERROR in ExecSql", e.getMessage());
			return null;
		}	
	}
	
	public String getInfoTabelleDatiDb()
	{
		try {
			openDataBase();
			Cursor cur =  _DataBase.rawQuery("Select count(*) from Categories ", null);
			
			cur.moveToFirst();
			int count= cur.getInt(0);
			cur.close();
			
			cur =  _DataBase.rawQuery("Select count(*) from MenuItems ", null);
			
			cur.moveToFirst();
			int count2 = cur.getInt(0);
			cur.close();
			
			return "DB OK\r\nTot in Cat : "+ count + "\r\nTot in Items : " + count2 ;
			
			
		} catch (Exception e) {
			Log.d("ERROR in ExecSql", e.getMessage());
			return "ERROR IN LETTURA DB : \r\n"+ e.getMessage();
		}	
	}
	
	//
	public Boolean checkIfDBFileExists()
	{
		String myPath = DB_PATH + DB_NAME;
        File f = new File(myPath);
        return f.exists();
		
	}//fM
	
	
	 /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		//do nothing - database already exist
    	}else{
 
    		//By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
        	this.getReadableDatabase();
 
        	try {
 
        		if(false)
        			copyDataBase();
 
    		} catch (IOException e) {
 
        		throw new Error("Error copying database");
 
        	}
    	}
 
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    public boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try
    	{    		
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    		
    	}
    	catch(SQLiteException e){
 
    		Log.d("Diego in checkDataBase","Errore : "+ e.getMessage());
 
    	}
 
    	if(checkDB != null)
    	{ 
    		checkDB.close(); 
    	}
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = _Context.getAssets().open(DB_NAME);
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    public void openDataBase() throws SQLException{
 
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
                
        if(_DataBase != null)
        {
        	if(!_DataBase.isOpen())
        		_DataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        }
        else 
        	_DataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
       
 
    }//fM
 
    @Override
	public synchronized void close() {
 
    	try{
    	    if(_DataBase != null)
    	    	_DataBase.close();
 
    	    super.close();
    	}
    	catch(Exception ex)
    	{}
    	
	}//fM
    
    /*
     * Chiamare sempre opendatabase prima
     * 
     */
    public synchronized Boolean insertDataIntoTable(String pTableName, ArrayList<ContentValues> pValues)
    {
    	try
    	{
	      
    		if(pValues == null) return false;    
    		
    		Log.d("In insertDataIntoTable " +pTableName,"Size : "+ pValues.size()+"" );
    		 
    		 openDataBase();
	        
    		 _DataBase.beginTransaction();
    		 
    		 for(int i = 0; i < pValues.size(); i++) 
 	        {
    			 _DataBase.insertOrThrow(pTableName, null, pValues.get(i));
 	        }
	        	      
    		 _DataBase.setTransactionSuccessful();
    		     		 
	        return true;
        
    	}
        catch(SQLException sqlerror)
        {
	        Log.v("ERROR DB in insertDataIntoTable",pTableName +" , "+ sqlerror.getMessage());	
	        return false;
        }
        finally
        {        	
        	if(_DataBase.inTransaction())
        		_DataBase.endTransaction();
        }
    	    
    }//fM
    
   

    //"%dT%m%Y %H%M%S" 
    public synchronized Time getCurrentDateTime()
    {
    	Time today = new Time(Time.getCurrentTimezone());
    	today.setToNow();
    	
    	return today;

    	
    }//fM
 
	
	
}//fC
